package com.sohoometer.web.excel;

import java.io.InputStream;
import java.text.DateFormat;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.nari.baseinfo.model.IHeatMeter;

public class ExcelInputUtils {

	public InputStream getTemplateFile() {
		InputStream templateFile = ExcelInputUtils.class
				.getResourceAsStream("heatMeterInput.xls");
		return templateFile;
	}

	public ExcelInputUtils() {
	}

	public HeatMeterImport parseHeatMeters(InputStream excel,
			HashMap<String, String> map) throws Exception {
		HeatMeterImport import1 = new HeatMeterImport();
		ArrayList<IHeatMeter> heatMeters = new ArrayList<IHeatMeter>();
		import1.setHeatMeter(heatMeters);
		Workbook workbook = WorkbookFactory.create(excel);
		Sheet sheet = workbook.getSheetAt(0);// 得到第一个热量表所在表单
		int lastRowNum = sheet.getLastRowNum();
		map = (HashMap<String, String>) MapUtils.invertMap(map);// 将id-projectName进行反向Map
		for (int i = 1; i <= lastRowNum; i++) {
			Row row = sheet.getRow(i);
			IHeatMeter heatMeter = new IHeatMeter();
			Cell cell = row.getCell(1);// 热量表表号
			String meterNo = "";
			if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
				double numericCellValue = cell.getNumericCellValue();
				Double meterNoDouble = Double.valueOf(numericCellValue);
				long longValue = meterNoDouble.longValue();
				meterNo = longValue + "";
			} else {
				meterNo = cell.getStringCellValue();
			}
			if (StringUtils.isBlank(meterNo)) {
				import1.setWrongMsg("第【" + i + 1 + "】行" + "-热量表号-有错误");
				return import1;
			}
			heatMeter.setMeterNo(meterNo);
			Cell cell2 = row.getCell(2);// 热量表型号
			String meterType = cell2.getStringCellValue();
			if (StringUtils.isNotBlank(meterType)) {
				heatMeter.setMeterType(meterType);
			}
			Cell cell3 = row.getCell(3);// 所属项目
			String project = cell3.getStringCellValue();
			project = StringUtils.trim(project);
			String projectId = map.get(project);
			if (StringUtils.isNotBlank(projectId)) {
				heatMeter.setProjectID(projectId);
			} else {
				import1.setSuccess(false);
				import1.setWrongMsg("确认Excel只有一个项目且项目名称正确  错误数据行数【" + i + 1
						+ "】");
				return import1;
			}
			if (StringUtils.isNotBlank(projectId) || !import1.isFlag()) {
				import1.setProjectID(projectId);
				import1.setFlag(true);
			}
			Cell cell4 = row.getCell(4);// 所属小区
			String community = cell4.getStringCellValue();
			community = community.trim();
			if (StringUtils.isNotBlank(community)) {
				heatMeter.setCommunityID(community);
			} else {
				import1.setSuccess(false);
				import1.setWrongMsg("小区名称为空  错误数据行数【" + i + 1 + "】");
				return import1;
			}
			Cell cell5 = row.getCell(5);// 所属楼栋
			String building = cell5.getStringCellValue();
			building = building.trim();
			if (StringUtils.isNotBlank(building)) {
				heatMeter.setBuildingID(building);
			} else {
				import1.setSuccess(false);
				import1.setWrongMsg("楼栋名称错误  错误数据行数【" + i + 1 + "】");
				return import1;
			}
			Cell cell6 = row.getCell(6); // 生产厂家
			String manufactory = cell6.getStringCellValue();
			if (StringUtils.isNotBlank(manufactory)) {
				heatMeter.setProduceFactory(manufactory);
			}
			Cell cell7 = row.getCell(7);// 管道口径
			String pipelDiameter = "";
			if (Cell.CELL_TYPE_NUMERIC == cell7.getCellType()) {
				pipelDiameter = cell7.getNumericCellValue() + "";
			} else {
				pipelDiameter = cell7.getStringCellValue();
			}
			if (StringUtils.isNotBlank(pipelDiameter)) {
				try {
					Double pipleDouble = Double.valueOf(pipelDiameter);
					heatMeter.setPipeDiameter(pipleDouble.doubleValue());
				} catch (Exception e) {
					import1.setSuccess(false);
					import1.setWrongMsg("管道口径信息错误  错误数据行数【" + i + "】");
					return import1;
				}
			}

			Cell cell8 = row.getCell(8);// 热表口径
			String meterDiameter = "";
			if (Cell.CELL_TYPE_NUMERIC == cell8.getCellType()) {
				meterDiameter = cell8.getNumericCellValue() + "";
			} else {
				meterDiameter = cell8.getStringCellValue();
			}
			if (StringUtils.isNotBlank(meterDiameter)) {
				try {
					Double meterDouble = Double.valueOf(meterDiameter);
					heatMeter.setMeterDiameter(meterDouble.doubleValue());
				} catch (Exception e) {
					import1.setSuccess(false);
					import1.setWrongMsg("热表口径信息错误  错误数据行数【" + i + "】");
					return import1;
				}
			}
			Cell cell9 = row.getCell(9);// 安装位置
			String installPosition = cell9.getStringCellValue();
			if ("供水".equals(installPosition)) {
				heatMeter.setInstallPosition("enter");
			}
			if ("回水".equals(installPosition)) {
				heatMeter.setInstallPosition("exit");
			}
			// {'enter':'供水', 'exit':'回水'} 安装位置映射
			Cell cell10 = row.getCell(10);// 安装环境
			String installEnvir = cell10.getStringCellValue();
			HashMap<String, String> envir = new HashMap<String, String>();
			envir.put("管井", "well");
			envir.put("管沟", "ditch");
			envir.put("设备间", "store");
			envir.put("场站", "platform");
			if (StringUtils.isNotBlank(installEnvir)) {
				String envirement = envir.get(installEnvir);
				heatMeter.setInstallEnvironment(envirement);
			}
			// {'well':'管井', 'ditch':'管沟', 'store':'设备间', 'platform':'场站'}
			// 安装环境映射
			Cell cell11 = row.getCell(11);// 安装日期
			int cellType = cell11.getCellType();
			// Date cellValue = cell10.getDateCellValue();
			String excelDate = "";
			if (Cell.CELL_TYPE_STRING == cellType) {
				excelDate = cell11.getStringCellValue();
			}
			DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
			if (StringUtils.isNotBlank(excelDate)) {
				try {
					Date parse = dateFormat.parse(excelDate);
					heatMeter.setInstallDate(parse);
				} catch (Exception e) {
				}
			}
			if (Cell.CELL_TYPE_NUMERIC == cellType) {
				try {
					Date installDate = cell11.getDateCellValue();
					heatMeter.setInstallDate(installDate);
				} catch (Exception e) {
					import1.setSuccess(false);
					import1.setWrongMsg("日期格式错误 数据格式为2013-09-20  错误数据行数【" + i
							+ "】");
					return import1;
				}
			}
			heatMeters.add(heatMeter);
		}
		import1.setSuccess(true);
		return import1;
	}

}
